﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spTSX_CreateLinkedServerMSX]
#-- Purpose:		Creates a linked server to the MSX for the Warehouse
#--	Last Update:	04/07/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spTSX_CreateLinkedServerMSX]
AS

--- Declare Local Variables
DECLARE	@sSQL nvarchar(MAX)

--- Create the query to add the MSX Linked Server
SELECT	@sSQL = 'USE [master]
DECLARE @msx_server sysname, @product nvarchar(2000)

SELECT	@msx_server = originating_server,
		@product = originating_server + N'' Kerberos''
FROM	[msdb].[dbo].[sysoriginatingservers]
WHERE	master_server = 1

IF NOT EXISTS (SELECT TOP 1 [name] FROM [msdb].[sys].[servers] WHERE [name] = @msx_server AND [is_linked] = 1)
  BEGIN
	EXEC	dbo.sp_addlinkedserver		@server = @msx_server, 
										@srvproduct=@product, 
										@provider=N''SQLNCLI'', 
										@datasrc=@msx_server, 
										@provstr=N''Integrated Security=SSPI'',
										@catalog=N''' + DB_NAME() + '''
	EXEC	dbo.sp_serveroption			@server=@msx_server, 
										@optname=N''rpc'', 
										@optvalue=N''true''
	EXEC	dbo.sp_serveroption			@server=@msx_server, 
										@optname=N''rpc out'', 
										@optvalue=N''true''
	EXEC	dbo.sp_addlinkedsrvlogin	@rmtsrvname = @msx_server, 
										@useself = N''True''
  END'

--- Create the linked server
EXEC	(@sSQL)